forum

Home / DeveloperSection / Forums / How do I use the GROUP BY clause to aggregate data in SQL Server?

Ask Question
Ravi Vishwakarma

Ravi Vishwakarma

Follow
Ravi Vishwakarma is a dedicated Software Developer with a passion for crafting efficient and innovative solutions. With a keen eye for detail and years of experience, he excels in developing robust software systems that meet client needs. His expertise spans across multiple programming languages and technologies, making him a valuable asset in any software development project.

Can you answer this question?

Answer

1 Answers

Ravi Vishwakarma

Ravi Vishwakarma

12-Jul-2024

The GROUP BY clause in SQL Server is used to group rows that have the same values in specified columns into aggregate data, such as sums, averages, counts, etc. Here is a step-by-step guide on how to use the GROUP BY clause:

Basic Syntax

SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY column1, column2;

Aggregate Functions

Common aggregate functions include:

  • SUM()
  • COUNT()
  • AVG()
  • MIN()
  • MAX()

Example Use Cases

1. Sum of Sales by Salesperson

Assume we have a table Sales with the following columns: SaleID, SaleAmount, SalesPersonID, and SaleDate.

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleAmount DECIMAL(10, 2),
    SalesPersonID INT,
    SaleDate DATE
);

INSERT INTO Sales (SaleID, SaleAmount, SalesPersonID, SaleDate)
VALUES
(1, 100.00, 1, '2024-01-01'),
(2, 200.00, 2, '2024-01-02'),
(3, 300.00, 1, '2024-01-03'),
(4, 150.00, 2, '2024-01-04'),
(5, 250.00, 3, '2024-01-05');

To calculate the total sales amount for each salesperson:

SELECT SalesPersonID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID;

Output:

SalesPersonID TotalSales
1 400.00
2 350.00
3 250.00

2. Count of Sales by Salesperson

To count the number of sales made by each salesperson:

SELECT SalesPersonID, COUNT(*) AS NumberOfSales
FROM Sales
GROUP BY SalesPersonID;

Output:

SalesPersonID NumberOfSales
1 2
2 2
3 1

3. Average Sale Amount by Salesperson

To calculate the average sale amount for each salesperson:

SELECT SalesPersonID, AVG(SaleAmount) AS AverageSaleAmount
FROM Sales
GROUP BY SalesPersonID;

Output:

SalesPersonID AverageSaleAmount
1 200.00
2 175.00
3 250.00

Using GROUP BY with Multiple Columns

You can group by multiple columns to get more granular aggregates.

SELECT SalesPersonID, SaleDate, SUM(SaleAmount) AS DailySales
FROM Sales
GROUP BY SalesPersonID, SaleDate;

Output:

SalesPersonID SaleDate DailySales
1 2024-01-01 100.00
1 2024-01-03 300.00
2 2024-01-02 200.00
2 2024-01-04 150.00
3 2024-01-05 250.00

Filtering Groups with HAVING

The HAVING clause is used to filter groups based on aggregate functions.

Example: Total Sales Greater than 300

SELECT SalesPersonID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
HAVING SUM(SaleAmount) > 300;

Output:

SalesPersonID TotalSales
1 400.00
2 350.00

 

Complete Example

Combining everything together, here is a full example including table creation, data insertion, and querying with GROUP BY and HAVING:

-- Create table
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleAmount DECIMAL(10, 2),
    SalesPersonID INT,
    SaleDate DATE
);

-- Insert sample data
INSERT INTO Sales (SaleID, SaleAmount, SalesPersonID, SaleDate)
VALUES
(1, 100.00, 1, '2024-01-01'),
(2, 200.00, 2, '2024-01-02'),
(3, 300.00, 1, '2024-01-03'),
(4, 150.00, 2, '2024-01-04'),
(5, 250.00, 3, '2024-01-05');

-- Query with GROUP BY and HAVING
SELECT SalesPersonID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
HAVING SUM(SaleAmount) > 300;

 

Read more

Write a query to retrieve the total number of employees in each department.

How do I use CTE to simplify complex queries in SQL Server?

Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL Server.

 

MindStick Training

Liked By